package com.pucong.gitee.database.backup.business.database.util;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.pucong.gitee.database.backup.business.database.mapper.AutoExeSqlLogMapper;
import com.pucong.gitee.database.backup.business.database.util.jdbcPool.JdbcConnectionManager;
import com.pucong.gitee.database.backup.common.entity.database.AutoDataSource;
import com.pucong.gitee.database.backup.common.entity.database.BackupExeSqlLog;
import com.pucong.gitee.database.backup.common.entity.database.dto.DatabaseSqlExecVo;
import com.pucong.gitee.database.backup.common.entity.database.dto.DatabaseTableIndexDto;
import com.pucong.gitee.database.backup.common.entity.database.vo.*;
import com.pucong.gitee.database.backup.common.selfenum.CommonBoolenEnum;
import com.pucong.gitee.database.backup.common.selfenum.DatabaseVersionEnum;
import com.pucong.gitee.database.backup.configuration.exception.BusinessException;
import com.pucong.gitee.database.backup.utils.ApplicationContextUtil;
import com.pucong.gitee.database.backup.utils.SqlUtils;
import com.pucong.gitee.database.backup.utils.UUID;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.DigestUtils;

import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.*;
import java.util.Date;
import java.util.*;

public abstract class AbstractOperationService implements OperationService {

    private static Logger log = LoggerFactory.getLogger(AbstractOperationService.class);

    protected static final String URL_PREFIX = "jdbc:postgresql://";

    protected static final String URL_LAST = "useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true";

    private CallableStatement callableStatement = null;
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rst = null;
    protected AutoDataSource autoDataSource = null;

    protected String driverClassName = null;
    protected String url = null;
    protected String username = null;
    protected String password = null;
    private String sessionId = null;
    private AutoExeSqlLogMapper autoExeSqlLogMapper = null;

    @Override
    public String getSessionId() {
        return sessionId;
    }

    @Override
    public void setSessionId(String sessionId) {
        this.sessionId = sessionId;
    }

    /**
     * 获取数据库名列表
     *
     * @return
     */
    public String getGET_BASE_LIST_SQL() {
        return "select datname dataBaseName " +
                " from pg_database t1 " +
                "where 1=1 " +
                "and datname not in ('template0','template1') " +
                "order by datname";
    }

    /**
     * 获取数据库下模式名列表
     *
     * @return
     */
    public String getGET_BASE_SCHEMA_LIST_SQL() {
        return "SELECT schema_name schemaName FROM information_schema.schemata where schema_name not in ('information_schema','pg_catalog','pg_toast_temp_1','pg_temp_1','pg_toast') and catalog_name=? ";
    }

    /**
     * 获取表字段列表
     *
     * @return
     */
    public String getGET_TABLE_LIST_SQL() {
        return "SELECT DISTINCT" +
                " a.schemaname," +
                " a.tablename tableName," +
                " b.reltuples as rowCounts," +
                " obj_description ( b.relfilenode, 'pg_class' ) AS tableComment " +
                "FROM" +
                " pg_tables A," +
                " pg_class b " +
                "WHERE A.tablename = b.relname" +
                " and A.schemaname=?";
    }

    /**
     * 获取表字段列表
     *
     * @return
     */
    public String getGET_TABLE_FILED_LIST_SQL() {
        return "SELECT a.attnum, " +
                "       a.attname AS filedName, " +
                "       t.typname AS filedType, " +
                "       a.attlen AS filedSize, " +
                "       a.atttypmod AS lengthvar, " +
                "       a.attnotnull AS notNull, " +
                "       b.description AS filedComment " +
                "  FROM pg_class c, " +
                "       pg_attribute a " +
                "       LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid, " +
                "       pg_type t,pg_namespace d  " +
                " WHERE 1=1 " +
                " and d.nspname=? " +
                " and  c.relname = ? " +
                " and c.relkind in ('r', 'v', 'm') " +
                " and d.nspname not in ('pg_catalog', 'pg_toast', 'information_schema') " +
                " and a.attnum > 0 " +
                " and c.relnamespace=d.oid " +
                " and a.attrelid = c.oid " +
                " and a.atttypid = t.oid " +
                " ORDER BY a.attnum ";
    }

    /**
     * 获取表名和字段名称
     *
     * @return
     */
    public String getGET_TABLE_FILED_NAME_LIST_SQL() {
        return "SELECT DISTINCT " +
                "       c.relname AS tablename, a.attnum,  " +
                "       a.attname AS filedName " +
                "  FROM pg_class c, " +
                "       pg_attribute a " +
                "       LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid, " +
                "       pg_type t,pg_namespace d  " +
                " WHERE 1=1 " +
                " and  d.nspname = ? " +
                " and c.relkind in ('r', 'v', 'm') " +
                " and d.nspname not in ('pg_catalog', 'pg_toast', 'information_schema') " +
                " and a.attnum > 0 " +
                " and c.relnamespace=d.oid " +
                " and a.attrelid = c.oid " +
                " and a.atttypid = t.oid " +
                " ORDER BY a.attnum ";
    }

    /**
     * 获取表索引列表
     *
     * @return
     */
    public String getGET_TABLE_INDEX_LIST_SQL() {
        return "SELECT " +
                "A.SCHEMANAME, " +
                "A.TABLENAME, " +
                "A.INDEXNAME, " +
                "A.TABLESPACE, " +
                "A.INDEXDEF, " +
                "B.AMNAME, " +
                "C.INDEXRELID, " +
                "C.INDNATTS, " +
                "C.INDISUNIQUE, " +
                "C.INDISPRIMARY, " +
                "C.INDISCLUSTERED, " +
                "D.DESCRIPTION " +
                "FROM " +
                "PG_AM B " +
                "LEFT JOIN PG_CLASS F ON B.OID = F.RELAM " +
                "LEFT JOIN PG_STAT_ALL_INDEXES E ON F.OID = E.INDEXRELID " +
                "LEFT JOIN PG_INDEX C ON E.INDEXRELID = C.INDEXRELID " +
                "LEFT OUTER JOIN PG_DESCRIPTION D ON C.INDEXRELID = D.OBJOID, " +
                "PG_INDEXES A " +
                "WHERE " +
                "A.SCHEMANAME = E.SCHEMANAME AND A.TABLENAME = E.RELNAME AND A.INDEXNAME = E.INDEXRELNAME " +
                "and A.SCHEMANAME = ? " +
                "and A.TABLENAME = ?";
    }

    public AbstractOperationService(AutoDataSource autoDataSource, AutoExeSqlLogMapper autoExeSqlLogMapper) {
        this.autoDataSource = autoDataSource;
        this.username = autoDataSource.getUsername();
        this.password = autoDataSource.getPassword();
        this.autoExeSqlLogMapper = autoExeSqlLogMapper;
        initUrl();
    }


    @Override
    public void initUrl() {
        this.url = URL_PREFIX + autoDataSource.getHost() + ":" + autoDataSource.getPort() + "/" + autoDataSource.getDatabaseName() + "?currentSchema=" + autoDataSource.getSchemaName() + "&" + URL_LAST;
    }

    @Override
    public void setDataBase(String databaseName) {
        autoDataSource.setDatabaseName(databaseName);
        initUrl();
    }

    @Override
    public void setSchema(String schema) {
        autoDataSource.setSchemaName(schema);
        initUrl();
    }

    @Override
    public List<String> getDataBaseList() {
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_BASE_LIST_SQL(), null);
        final List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        List<String> strings = new ArrayList<>(resultList.size());
        for (Map<String, Object> stringObjectMap : resultList) {
            Collection<Object> values = stringObjectMap.values();
            for (Object value : values) {
                strings.add(value.toString());
            }
        }
        return strings;
    }

    @Override
    public List<String> getSchemaList(String databaseName) {
        List<String> resultStrList = new ArrayList<>();
        Object[] params = new Object[1];
        params[0] = databaseName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_BASE_SCHEMA_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            resultStrList.add(stringObjectMap.get("schemaname").toString());
        }
        return resultStrList;
    }

    @Override
    public List<DatabaseTableVo> getTableListByDBInfo(String databaseName, String schemaName) {
        List<DatabaseTableVo> resultTableList = new ArrayList<>();
        Object[] params = new Object[1];
        params[0] = schemaName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DatabaseTableVo databaseTableVo = new DatabaseTableVo();
            String tableName = Optional.ofNullable(stringObjectMap.get("tablename")).map(String::valueOf).orElse(StringUtils.EMPTY);
            String count = Optional.ofNullable(stringObjectMap.get("rowcounts")).map(String::valueOf).orElse("0");
            String tableComment = Optional.ofNullable(stringObjectMap.get("tablecomment")).map(String::valueOf).orElse(StringUtils.EMPTY);
            databaseTableVo.setUuid(DigestUtils.md5DigestAsHex((autoDataSource.getUuid() + autoDataSource.getDatabaseName() + autoDataSource.getSchemaName() + tableName).getBytes()));
            databaseTableVo.setDataSize(new BigDecimal(count));
            databaseTableVo.setDataSourceId(autoDataSource.getUuid());
            databaseTableVo.setTableName(tableName);
            databaseTableVo.setTableComment(tableComment);
            resultTableList.add(databaseTableVo);
        }
        return resultTableList;
    }

    @Override
    public List<DbTableFiledVo> getTableFiledListByDBInfo(String databaseName, String schemaName) {
        List<DbTableFiledVo> resultTableList = new ArrayList<>();
        Object[] params = new Object[1];
        params[0] = schemaName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_FILED_NAME_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DbTableFiledVo dbTableFiledVo = new DbTableFiledVo();
            String filedName = getMapValue(stringObjectMap, "filedname");
            String tableName = getMapValue(stringObjectMap, "tablename");
            dbTableFiledVo.setTableName(tableName);
            dbTableFiledVo.setFiledName(filedName);
            resultTableList.add(dbTableFiledVo);
        }
        return resultTableList;
    }

    @Override
    public List<DatabaseTableFiledVo> getTableFiledListByDBInfo(String databaseName, String schemaName, String tableName) {
        List<DatabaseTableFiledVo> resultTableList = new ArrayList<>();
        Object[] params = new Object[2];
        params[0] = schemaName;
        params[1] = tableName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_FILED_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DatabaseTableFiledVo databaseTableFiledVo = new DatabaseTableFiledVo();
            String filedName = getMapValue(stringObjectMap, "filedname");
            String filedSize = getMapValue(stringObjectMap, "filedsize");
            String filedLength = getMapValue(stringObjectMap, "lengthvar");
            String filedType = getMapValue(stringObjectMap, "filedtype");
            String primaryKey = getMapValue(stringObjectMap, "primarykey");
            String notNull = getMapValue(stringObjectMap, "notNull");
            String defaultValue = getMapValue(stringObjectMap, "defaultvalue");
            String filedComment = getMapValue(stringObjectMap, "filedcomment");
            databaseTableFiledVo.setFiledName(filedName);
            if ("int2,int4,int8,".indexOf(filedType) >= 0) {
                databaseTableFiledVo.setFiledSize(filedSize);
            } else {
                databaseTableFiledVo.setFiledSize(filedLength);
            }
            databaseTableFiledVo.setFiledType(filedType);
            databaseTableFiledVo.setPrimaryKey(isTrue(primaryKey));
            databaseTableFiledVo.setNotNull(isTrue(notNull));
            databaseTableFiledVo.setFiledComment(filedComment);
            databaseTableFiledVo.setDefaultValue(defaultValue);
            resultTableList.add(databaseTableFiledVo);
        }
        return resultTableList;
    }

    private CommonBoolenEnum isTrue(String val) {
        String val2 = val.toLowerCase();
        if ("PRI".equals(val2)) {
            return CommonBoolenEnum.是;
        }
        if ("YES".equals(val2)) {
            return CommonBoolenEnum.是;
        }
        if ("t".equals(val2)) {
            return CommonBoolenEnum.是;
        }
        return CommonBoolenEnum.否;
    }


    protected String getMapValue(Map<String, Object> map, String key) {
        Object object = map.get(key);
        return object == null ? "" : object.toString();
    }

    @Override
    public ExeSqlResult getCountByQuerySql(String sql) {
        String sqlCount = "select count(0) count from (" + sql + ") asdqweas";
        Object[] params = null;
        ExeSqlResult exeSqlResult = this.executeQuery(sqlCount, params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        String count = resultList.get(0).get("count").toString();
        exeSqlResult.setResultInteger(new BigDecimal(count));
        return exeSqlResult;
    }

    @Override
    public Boolean runSqlByScriptRunner(String sqlPath) {
        try {
            ScriptRunner runner = new ScriptRunner(conn);
            runner.setEscapeProcessing(false);
            runner.setSendFullScript(true);
            runner.runScript(new InputStreamReader(new FileInputStream(sqlPath), "UTF-8"));
        } catch (Exception e) {
            log.error("执行sql文件异常", e);
            throw new BusinessException("执行sql文件异常");
        }

        return true;
    }

    /**
     * 获取连接
     */
    @Override
    public Boolean getConnection() {
        // 获取连接
        log.info("连接数据库", sessionId, url, username, password);
        conn = JdbcConnectionManager.getConnection(sessionId, url, username, password, driverClassName);
        if (conn == null) {
            return false;
        }
        return true;
    }

    /**
     * 关闭所有资源
     */
    @Override
    public void closeAll() {
        // 关闭结果集对象
        if (rst != null) {
            try {
                rst.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常", e);
            }
        }

        // 关闭PreparedStatement对象
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常", e);
            }
        }

        // 关闭CallableStatement 对象
        if (callableStatement != null) {
            try {
                callableStatement.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常", e);
            }
        }

        // 关闭Connection 对象
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常", e);
            }
        }
    }

    @Override
    public String generatePageSql(String sql, Integer pageNum, Integer pageSize) {
        throw new BusinessException("不支持的操作");
    }

    /**
     * insert update delete SQL语句的执行的统一方法
     *
     * @param sql    SQL语句
     * @param params 参数数组，若没有参数则为null
     * @return 受影响的行数
     */
    @Override
    public int executeUpdate(String sql, Object[] params) {
        // 受影响的行数
        int affectedLine = 0;
        try {
            log.info("执行sql: " + sql);
            // 插入执行sql
            BackupExeSqlLog insertLog = new BackupExeSqlLog();
            insertLog.setUuid(UUID.uuid());
            insertLog.setExeSql(sql);
            insertLog.setExeParams("");
            insertLog.setDatabaseId(autoDataSource.getUuid());
            insertLog.setDatabaseName(autoDataSource.getDatabaseName());
            insertLog.setSchemaName(autoDataSource.getSchemaName());
            insertLog.setExeTime(new Date());
            autoExeSqlLogMapper.insert(insertLog);
            // 调用SQL
            Statement statement = conn.createStatement();

            /**
             * 在此 PreparedStatement 对象中执行 SQL 语句，
             *  该语句必须是一个 SQL 数据操作语言（Data Manipulation Language，DML）语句，比如 INSERT、UPDATE 或 DELETE
             *  语句；或者是无返回内容的 SQL 语句，比如 DDL 语句。
             */
            affectedLine = statement.executeUpdate(sql);

        } catch (SQLException e) {
            log.error("执行sql异常", e);
            throw new BusinessException("执行sql异常" + e.getMessage());
        } finally {
            this.closeAll();
        }
        return affectedLine;
    }

    @Override
    public ExeSqlResult executeQuery(String sql, Object[] params) {
        Date date1 = new Date();
        final Boolean connection = this.getConnection();
        if (!connection) {
            throw new BusinessException("获取数据库连接失败:" + url);
        }
        ExeSqlResult exeSqlResult = new ExeSqlResult();
        // 执行SQL获得结果集
        try {
            log.info("执行sql: " + sql);
            StringBuffer sb = new StringBuffer();
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    if (params[i] != null) {
                        sb.append(params[i].toString().trim() + ",");
                    }
                }
            }
            String newStr = sb.toString();
            log.info("执行sql 参数: " + newStr);
            // 插入执行sql
            BackupExeSqlLog insertLog = new BackupExeSqlLog();
            insertLog.setUuid(UUID.uuid());
            insertLog.setExeSql(sql);
            insertLog.setExeParams(newStr);
            insertLog.setDatabaseId(autoDataSource.getUuid());
            insertLog.setDatabaseName(autoDataSource.getDatabaseName());
            insertLog.setSchemaName(autoDataSource.getSchemaName());
            insertLog.setExeTime(new Date());
            autoExeSqlLogMapper.insert(insertLog);
            // 调用SQL
            pst = conn.prepareStatement(sql);

            // 参数赋值
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pst.setObject(i + 1, params[i]);
                }
            }

            // 执行
            rst = pst.executeQuery();
            // 创建ResultSetMetaData对象
            ResultSetMetaData rsmd = null;

            // 结果集列数
            int columnCount = 0;
            rsmd = rst.getMetaData();

            // 获得结果集列数
            columnCount = rsmd.getColumnCount();

            // 创建List
            List<Map<String, Object>> list = new ArrayList<>();
            List<ResultFiledVo> filedList = new ArrayList<>();

            for (int i = 1; i <= columnCount; i++) {
                ResultFiledVo resultFiledVo = new ResultFiledVo();
                resultFiledVo.setFiledName(rsmd.getColumnName(i));
                resultFiledVo.setFiledType(rsmd.getColumnTypeName(i));
                filedList.add(resultFiledVo);
            }
            // 将ResultSet的结果保存到List中
            while (rst.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(rsmd.getColumnLabel(i), rst.getObject(i));
                }
                //每一个map代表一条记录，把所有记录存在list中
                list.add(map);
            }
            exeSqlResult.setResultList(list);
            exeSqlResult.setFiledList(filedList);
            Date date2 = new Date();
            exeSqlResult.setExecTime(date2.getTime() - date1.getTime());
        } catch (SQLException e) {
            log.error("执行sql异常", e);
            throw new BusinessException("执行sql异常" + e.getMessage());
        } finally {
            this.closeAll();
        }
        return exeSqlResult;
    }

    @Override
    public RunSqlResultPage executeQueryPage(String sql, Object[] params) {
        return executeQueryPage(1, 10, sql, params);
    }

    @Override
    public RunSqlResultPage executeQueryPage(Integer pageNum, Integer pageSize, String sql, Object[] params) {
        String sqlPage = this.generatePageSql(sql, pageNum, pageSize);
        // 查询总数
        ExeSqlResult countResult = this.getCountByQuerySql(sql);
        ExeSqlResult exeSqlResult = null;
        if (countResult.getResultInteger().compareTo(BigDecimal.ZERO) == 0) {
            exeSqlResult = new ExeSqlResult();
            exeSqlResult.setExecTime(countResult.getExecTime());
            exeSqlResult.setFiledList(new ArrayList<>());
            exeSqlResult.setResultList(new ArrayList<>());
        } else {
            // 获取数据
            exeSqlResult = executeQuery(sqlPage, params);
        }
        Page<Map<String, Object>> listIPage = new Page();
        // 数据
        listIPage.setRecords(exeSqlResult.getResultList());
        // 条数
        listIPage.setTotal(Long.parseLong(countResult.getResultInteger().toString()));
        // 当前页数
        listIPage.setCurrent(1);
        // 总页数
        listIPage.setPages(countResult.getResultInteger().divide(new BigDecimal(pageSize), RoundingMode.UP).intValue());
        // 一页大小
        listIPage.setSize(pageSize);

        RunSqlResultPage runSQLResultPage = new RunSqlResultPage();
        runSQLResultPage.setExecResultMapPage(listIPage);
        runSQLResultPage.setFiledList(exeSqlResult.getFiledList());
        runSQLResultPage.setExecTime(exeSqlResult.getExecTime());
        runSQLResultPage.setExecSql(sqlPage);
        runSQLResultPage.setExecSqlParams(params);
        runSQLResultPage.setSessionId(sessionId);
        return runSQLResultPage;
    }

    @Override
    public void stopExec() {
        try {
            this.pst.close();
        } catch (SQLException e) {
            log.error("停止执行失败", e);
            throw new BusinessException("停止执行失败" + e.getMessage());
        }
    }

    @Override
    public void trueAutoCommit() {
        try {
            this.conn.setAutoCommit(true);
        } catch (SQLException e) {
            log.error("关闭事物失败", e);
            throw new BusinessException("关闭事物失败" + e.getMessage());
        }
    }

    @Override
    public void falseAutoCommit() {
        try {
            this.conn.setAutoCommit(false);
        } catch (SQLException e) {
            log.error("开启事物失败", e);
            throw new BusinessException("开启事物失败" + e.getMessage());
        }
    }

    @Override
    public void commitTransactional() {
        try {
            this.conn.commit();
        } catch (SQLException e) {
            log.error("提交事物失败", e);
            throw new BusinessException("提交事物失败" + e.getMessage());
        }
    }

    @Override
    public Boolean dropDatabase(String name) {
        String updateSql = "DROP DATABASE IF EXISTS " + name;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean renameDatabase(String name, String toName) {
        String updateSql = "UPDATE pg_database" +
                "        SET datname = '" + toName + "'" +
                "        WHERE datname = '" + name + "'";
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean dropDataSchema(String name) {
        String updateSql = "drop schema " + name + " cascade ";
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean renameDataSchema(String name, String toName) {
        String updateSql = "ALTER SCHEMA  " + name + " RENAME TO " + toName;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean dropDataTable(String name) {
        String updateSql = "DROP TABLE " + name;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean renameDataTable(String name, String toName) {
        String updateSql = "alter table " + name + " rename to " + toName;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean addDataTableFiled(String tableName, DatabaseTableFiledVo addDatabaseTableFiledVo) {
        String filed = addDatabaseTableFiledVo.getFiledName() + " " + addDatabaseTableFiledVo.getFiledType() + "(" + addDatabaseTableFiledVo.getFiledSize() + ")";
        String updateSql = "ALTER TABLE " + tableName + " ADD " + filed;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean deleteDataTableFiled(String tableName, String tableFiled) {
        String updateSql = "alter table " + tableName + " drop column if exists " + tableFiled;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1 ? true : false;
    }

    @Override
    public Boolean alertDataTableFiled(String tableName, String tableFiled, DatabaseTableFiledVo toDatabaseTableFiledVo) {
        String type = toDatabaseTableFiledVo.getFiledType() + "(" + toDatabaseTableFiledVo.getFiledSize() + ")";

        String updateNameSql = "alter table " + tableName + " RENAME " + tableFiled + " to " + toDatabaseTableFiledVo.getFiledName();
        String updateSql = "alter table " + tableName + " alter COLUMN  " + toDatabaseTableFiledVo.getFiledName() + " type " + type;
        Object[] params = null;
        int i = this.executeUpdate(updateNameSql, params);
        i += this.executeUpdate(updateSql, params);
        return i > 0 ? true : false;
    }

    @Override
    public Boolean addFiledPk(String tableName, String tableFiled) {
        String pkUpdate = "ALTER TABLE \"" + tableName + "\" ADD CONSTRAINT \"" + tableFiled + "_pkey\" PRIMARY KEY (\"" + tableFiled + "\");";
        Object[] params = null;
        int i = this.executeUpdate(pkUpdate, params);
        return i > 0 ? true : false;
    }

    @Override
    public Boolean deleteFiledPk(String tableName, String tableFiled) {
        String pkUpdate = "ALTER TABLE \"" + tableName + "\" DROP CONSTRAINT \"" + tableFiled + "_pkey\"";
        Object[] params = null;
        int i = this.executeUpdate(pkUpdate, params);
        return i > 0 ? true : false;
    }

    @Override
    public Boolean dropTable(String tableName) {
        String sql = "drop TABLE \"" + tableName + "\"";
        Object[] params = null;
        int i = this.executeUpdate(sql, params);
        return i > 0 ? true : false;
    }

    @Override
    public Boolean clearTableData(String tableName) {
        String sql = "delete TABLE \"" + tableName + "\"";
        Object[] params = null;
        int i = this.executeUpdate(sql, params);
        return i > 0 ? true : false;
    }

    @Override
    public Boolean deleteTableData(String tableName, List<Map<String, Object>> dataList) {
        List<DatabaseSqlExecVo> deleteSqlListByList = SqlUtils.getDeleteSqlListByList(dataList, tableName);
        for (DatabaseSqlExecVo vo : deleteSqlListByList) {
            this.executeUpdate(vo.getSql(), vo.getParams());
        }
        return dataList.size() > 0 ? true : false;
    }

    /**
     * 生成ddl
     *
     * @param tableName
     * @return
     */
    @Override
    public String getDdl(String tableName) {
        String fileName = tableName + UUID.uuid() + "ddl.sql";
        String sqlFileDir = ApplicationContextUtil.getTmpPath() + File.separator + fileName;
        File file = new File(sqlFileDir);

        StringBuffer sql = new StringBuffer();
        DatabaseMetaData odmd = null;
        try {
            odmd = conn.getMetaData();
            ResultSet pkRSet = odmd.getPrimaryKeys(null, null, tableName);
            ResultSet rscol = odmd.getColumns(null, null, tableName, null);
            ResultSet inset = odmd.getIndexInfo(null, null, tableName, false, true);

            String commnt = "";

            String colstr = "";
            // 遍历列
            while (rscol.next()) {
                String ColumnName = rscol.getString(4);
                String ColumnTypeName = rscol.getString(6);
                String REMARKS = rscol.getString(12);
                if (StringUtils.isNotBlank(REMARKS)) {
                    commnt = commnt + "COMMENT ON " + tableName + "." + ColumnName + " IS '" + REMARKS + "' ; \n";
                }
                int displaySize = rscol.getInt(7);
                int scale = rscol.getInt(9);
                // int Precision = displaySize-scale;
                if (StringUtils.isNotBlank(colstr)) {
                    colstr = colstr + ",\n";
                }
                colstr = colstr + "\t" + ColumnName + "\t";
                if (StringUtils.indexOf(ColumnTypeName, "identity") >= 0) {
                    colstr = colstr + ColumnTypeName + "(1,1)";
                } else if (StringUtils.equalsIgnoreCase(ColumnTypeName, "timestamp")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "int")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "datetime")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "long")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "date")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "text")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "image")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "bit")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "ntext")
                ) {
                    colstr = colstr + ColumnTypeName + "";
                } else if (StringUtils.equalsIgnoreCase(ColumnTypeName, "decimal")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "number")
                        || StringUtils.equalsIgnoreCase(ColumnTypeName, "double")) {
                    if (scale == 0) {
                        colstr = colstr + ColumnTypeName + "(" + displaySize + ")";
                    } else {
                        colstr = colstr + ColumnTypeName + "(" + displaySize + "," + scale + ")";
                    }
                } else {
                    colstr = colstr + ColumnTypeName + "(" + displaySize + ")";
                }
                String defaultstr = rscol.getString(13);
                if (defaultstr != null) {
                    colstr = colstr + "\t default " + defaultstr;
                }
                if (rscol.getInt(11) == DatabaseMetaData.columnNoNulls) {
                    colstr = colstr + "\tnot null";
                } else if (rscol.getInt(11) == DatabaseMetaData.columnNullable) {
                    // sql.append("\tnull");
                }
            }
            // 遍历索引
            String indexu = "";
            while (inset.next()) {
                if (inset.getInt(7) == DatabaseMetaData.tableIndexOther) {
                    indexu = indexu + "CREATE UNIQUE INDEX " + inset.getString(6) + " ON " + inset.getString(5) + "(" + inset.getString(9) + ");\n";
                }
            }
            // 遍历主键、外键
            String pkcolstr = "";
            while (pkRSet.next()) {
                if (StringUtils.isNotBlank(pkcolstr)) {
                    pkcolstr = pkcolstr + ",\n";
                } else {
                    if (StringUtils.isNotBlank(colstr)) {
                        colstr = colstr + ",\n";
                    }
                }
                pkcolstr = pkcolstr + "\tconstraint \"" + pkRSet.getObject(6) + "\" primary key (" + pkRSet.getObject(4) + ")";
            }
            sql.append("create table " + tableName + "\n(" + colstr + pkcolstr + "\n)\n\n");
            sql.append("\n" + commnt + "" + indexu + "\n");
        } catch (Exception e) {
            log.error("备份失败:获取输出流失败", e);
            throw new BusinessException("备份失败:获取输出流失败");
        }
        // 保存内容到文件
        FileWriter fw = null;
        try {
            if (!file.exists()) {
                file.createNewFile();
            }
            // true表示追加
            fw = new FileWriter(file, true);
            //向文件中写内容
            fw.write(sql.toString());
            //换行
            fw.write("\r\n");
            fw.flush();
        } catch (IOException e) {
            log.error("输出内容失败", e);
            throw new BusinessException("输出内容失败：" + e.getMessage());
        } finally {
            if (fw != null) {
                try {
                    fw.close();
                } catch (IOException e) {
                    log.error("关闭失败", e);
                    throw new BusinessException("关闭失败：" + e.getMessage());
                }
            }
        }
        return sqlFileDir;
    }

    /**
     * 生成dml
     *
     * @param tableName
     * @return
     */
    @Override
    public String getDml(String tableName) {
        // 创建sql文件
        String fileName = tableName + UUID.uuid() + "dml.sql";
        String sqlFileDir = ApplicationContextUtil.getTmpPath() + File.separator + fileName;
        File file = new File(sqlFileDir);
        // 获取每张表的数据量
        ExeSqlResult countByQuerySql = getCountByQuerySql("select count(0) from " + tableName);
        BigDecimal dataTotal = countByQuerySql.getResultInteger();
        if (dataTotal.compareTo(new BigDecimal("0")) == 0) {
            log.debug(tableName + " 表数据量为0");
        } else {
            try {
                file.createNewFile();
                // 计算页数，进行分页导出
                Integer pageSize = 5000;
                BigDecimal pageTotalBigDecimal = dataTotal.add(new BigDecimal(pageSize - 1)).divide(new BigDecimal(pageSize), 0, BigDecimal.ROUND_UP);
                int pageTotal = pageTotalBigDecimal.intValue();
                BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(new FileOutputStream(file));
                for (int pageNum = 1; pageNum <= pageTotal; pageNum++) {
                    // 获取表的insert语句
                    RunSqlResultPage runSqlResultPage = executeQueryPage(pageNum, pageSize, "select * from " + tableName, null);
                    List<Map<String, Object>> records = runSqlResultPage.getExecResultMapPage().getRecords();
                    List<String> dataInserts = SqlUtils.getInsertSqlListByList(records, tableName);
                    for (int k = 0; k < dataInserts.size(); k++) {
                        bufferedOutputStream.write(dataInserts.get(k).getBytes());
                        bufferedOutputStream.write("\r\n".getBytes());
                    }
                    bufferedOutputStream.flush();
                }
                log.info("dml文件生成结束：" + fileName);
                bufferedOutputStream.close();
            } catch (Exception e) {
                log.error("dml文件生成失败:获取输出流失败", e);
                throw new BusinessException("dml文件生成失败:获取输出流失败");
            }
        }
        return sqlFileDir;
    }

    @Override
    public Boolean toUp(String tableName, String fileName) {
        throw new BusinessException("pg数据库不支持修改字段排序，请重新创建表");
    }

    @Override
    public Boolean toDown(String tableName, String fileName) {
        throw new BusinessException("pg数据库不支持修改字段排序，请重新创建表");
    }

    @Override
    public Boolean deleteFiled(String tableName, String fileName) {
        String pkUpdate = "ALTER TABLE \"" + tableName + "\" DROP column " + fileName + ";";
        Object[] params = null;
        int i = this.executeUpdate(pkUpdate, params);
        return i > 0 ? true : false;
    }

    @Override
    public Boolean addFiled(String tableName, DatabaseTableFiledVo filedVo) {
        // 添加字段
        String pkUpdate = "ALTER TABLE \"" + tableName + "\" ADD " + filedVo.getFiledName() + " " + filedVo.getFiledType() + ";";
        if(filedVo.getNotNull() == CommonBoolenEnum.是){
            pkUpdate = "ALTER TABLE \"" + tableName + "\" ADD " + filedVo.getFiledName() + " " + filedVo.getFiledType() + " not null;";
        }
        if ("varchar".equals(filedVo.getFiledType())) {
            pkUpdate = "ALTER TABLE \"" + tableName + "\" ADD " + filedVo.getFiledName() + " " + filedVo.getFiledType() + "(" + filedVo.getFiledSize() + ");";
            if(filedVo.getNotNull() == CommonBoolenEnum.是){
                pkUpdate = "ALTER TABLE \"" + tableName + "\" ADD " + filedVo.getFiledName() + " " + filedVo.getFiledType() + "(" + filedVo.getFiledSize() + ") not null;";
            }
        }

        Object[] params = null;
        int i = this.executeUpdate(pkUpdate, params);
        // 添加字段注释
        String pkUpdateCommon = "COMMENT ON COLUMN \"" + tableName + "\".\"" + filedVo.getFiledName() + "\" IS '" + filedVo.getFiledComment() + "';";
        int i2 = this.executeUpdate(pkUpdateCommon, null);
        return true;
    }

    @Override
    public Boolean updateFiled(String tableName, String filedNameOld, DatabaseTableFiledVo filedVo) {
        // 修改字段类型
        String pkUpdate = "ALTER TABLE \"" + tableName + "\" alter COLUMN " + filedNameOld + " " + filedVo.getFiledType() + ";";
        if(filedVo.getNotNull() == CommonBoolenEnum.是){
            pkUpdate = "ALTER TABLE \"" + tableName + "\" alter COLUMN " + filedNameOld + " " + filedVo.getFiledType() + " not null;";
        }
        if ("varchar".equals(filedVo.getFiledType())) {
            pkUpdate = "ALTER TABLE \"" + tableName + "\" alter COLUMN " + filedNameOld + " " + filedVo.getFiledType() + "(" + filedVo.getFiledSize() + ");";
            if(filedVo.getNotNull() == CommonBoolenEnum.是){
                pkUpdate = "ALTER TABLE \"" + tableName + "\" alter COLUMN " + filedNameOld + " " + filedVo.getFiledType() + "(" + filedVo.getFiledSize() + ") not null;";
            }
        }
        int i = this.executeUpdate(pkUpdate, null);
        // 修改字段名称
        String pkUpdate2 = "alter table \"" + tableName + "\" rename column " + filedNameOld + " to " + filedVo.getFiledName() + ";";
        int i2 = this.executeUpdate(pkUpdate2, null);
        // 修改字段注释
        String pkUpdateCommon = "COMMENT ON COLUMN \"" + tableName + "\".\"" + filedVo.getFiledName() + "\" IS '" + filedVo.getFiledComment() + "';";
        int i3 = this.executeUpdate(pkUpdateCommon, null);
        return true;
    }

    @Override
    public String[] getTableFiledTypeList() {
        DatabaseVersionEnum databaseVersion = autoDataSource.getDatabaseVersion();
        return new String[]{"bigint", "bigserial", "bit", "bit varying", "boolean", "box", "bytea", "varchar", "character", "cidr", "circle", "date", "double precision", "inet", "integer", "interval", "line", "lseg", "macaddr", "numeric", "path", "point", "polygon", "real", "smallint", "serial", "text", "time", "time with time zone", "timestamp", "timestamp with time zone", "tsquery", "tsvector", "txid_snapshot", "uuid", "xml"};
    }

    @Override
    public List<DatabaseTableIndexVo> getTableIndexList(String schemaName, String tableName) {
        List<DatabaseTableIndexVo> resultTableList = new ArrayList<>();
        Object[] params = new Object[2];
        params[0] = schemaName;
        params[1] = tableName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_INDEX_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DatabaseTableIndexVo indexVo = new DatabaseTableIndexVo();
            String indexname = getMapValue(stringObjectMap, "indexname");
            String indexDef = getMapValue(stringObjectMap, "indexdef");
            String amName = getMapValue(stringObjectMap, "amname");
            String indisunique = getMapValue(stringObjectMap, "indisunique");
            String indisclustered = getMapValue(stringObjectMap, "indisclustered");
            String description = getMapValue(stringObjectMap, "description");
            indexVo.setIndexName(indexname);
            indexVo.setIndexType(amName);
            indexVo.setFiledNames(getIndexFiled(indexDef));
            indexVo.setDisUnique(isTrue(indisunique));
            indexVo.setDisClustered(isTrue(indisclustered));
            indexVo.setIndexComment(description);
            resultTableList.add(indexVo);
        }
        return resultTableList;
    }

    /**
     * 排除括号及括号内的数据
     *
     * @param indexDef
     * @return
     */
    private String getIndexFiled(String indexDef) {
        String substring = indexDef.substring(indexDef.indexOf("(") + 1, indexDef.indexOf(")"));
        return substring;
    }

    @Override
    public Boolean addIndex(String schemaName, String tableName, DatabaseTableIndexDto indexDto) {
        // CREATE UNIQUE INDEX pg_am_oid_index ON pg_catalog.pg_am USING btree (oid)
        // create index index_name on table_name(column_name1,column_name2...)
        // 创建索引
        String target = schemaName + "." + tableName;
        String pkUpdateCommon = "CREATE UNIQUE INDEX " + indexDto.getIndexName() + " ON " + target + " USING " + indexDto.getIndexType() + " (" + indexDto.getFiledNames() + ");";
        if (indexDto.getDisUnique() == CommonBoolenEnum.是) {
            pkUpdateCommon = "CREATE INDEX " + indexDto.getIndexName() + " ON " + target + " USING " + indexDto.getIndexType() + " (" + indexDto.getFiledNames() + ");";
        }
        int i2 = this.executeUpdate(pkUpdateCommon, null);
        return true;
    }

    @Override
    public Boolean updateIndex(String schemaName, String tableName, String indexName, DatabaseTableIndexDto indexDto) {
        deleteIndex(schemaName, tableName, indexName);
        return addIndex(schemaName, tableName, indexDto);
    }

    @Override
    public Boolean deleteIndex(String schemaName, String tableName, String indexName) {
        // drop index index_name;
        // 删除索引
        String pkUpdateCommon = "drop index " + indexName + ";";
        int i2 = this.executeUpdate(pkUpdateCommon, null);
        return true;
    }

    @Override
    public String[] getTableIndexTypeList() {
        DatabaseVersionEnum databaseVersion = autoDataSource.getDatabaseVersion();
        return new String[]{"B-Tree", "Gash", "GiST", "GIN", "SP-GiSt", "BRIN"};
    }

    @Override
    public  Boolean alterPk(String schemaName, String tableName, String filedName, Boolean pk){
        String pkUpdate = "ALTER TABLE \"" + tableName + "\" add primary key (" + filedName + ");";
        if(!pk){
            pkUpdate = "ALTER TABLE \"" + tableName + "\" drop constraint " + filedName + "_pkey;";
        }
        int i2 = this.executeUpdate(pkUpdate, null);
        return true;
    }
}
